USE CelestialDB
GO
/*****************************************************************************************/
/* Check if email is in database*/
CREATE PROCEDURE uspEmailExists
	@Email	VARCHAR(50)
AS
SELECT Email FROM tblCustomers WHERE Email = @Email
GO
/*****************************************************************************************/
/* Validate Login */
CREATE PROCEDURE uspValidateCustomerLogin(
	@Email VARCHAR(50),
	@Password BINARY(16)
)
AS
BEGIN
	
SELECT * FROM tblCustomers WHERE Email = @Email AND [Password]=@Password
END
GO
/*****************************************************************************************/
/* Insert Customer */
CREATE PROCEDURE uspInsertCustomer
	@Password			BINARY(16),
	@UserLevel			INT,
	@RegisteredOn		DATE,
	
	@FirstName			VARCHAR(30),
	@LastName			VARCHAR(30),
	@Email				VARCHAR(50),
	@Contact			VARCHAR(15),
	@DOB				DATE,
	
	@Country			VARCHAR(30),
	@City				VARCHAR(30),
	@State				VARCHAR(30),
	@AddressLine1		VARCHAR(30),
	@AddressLine2		VARCHAR(30),
	@PostalCode			VARCHAR(10),
	
	@CreditCardNumber	CHAR(16),
	@CreditCardType		INT,
	@CreditCardCVV		VARCHAR(3),
	@CreditCardExpiry	DATE,
	@CreditCardHolder	VARCHAR(30),
	
	@IsActive			BIT
AS
BEGIN
	INSERT INTO tblCustomers 
	VALUES(@Password, @UserLevel, @RegisteredOn, @FirstName, @LastName, @Email, @Contact, @DOB,
		   @Country, @City, @State, @AddressLine1, @AddressLine2, @PostalCode,
		   @CreditCardNumber, @CreditCardType, @CreditCardCVV, @CreditCardExpiry, @CreditCardHolder,
		   @IsActive)
END
GO
/*****************************************************************************************/
/* Update Customer */
CREATE PROCEDURE uspUpdateCustomer
	@CustomerID			INT,
	@UserLevel			INT,
	@Password			BINARY(16),
	@RegisteredOn		DATE,
	
	@FirstName			VARCHAR(30),
	@LastName			VARCHAR(30),
	@Email				VARCHAR(50),
	@Contact			VARCHAR(15),
	@DOB				DATE,
	
	@Country			VARCHAR(30),
	@City				VARCHAR(30),
	@State				VARCHAR(30),
	@AddressLine1		VARCHAR(30),
	@AddressLine2		VARCHAR(30),
	@PostalCode			VARCHAR(10),
	
	@CreditCardNumber	CHAR(16),
	@CreditCardType		INT,
	@CreditCardCVV		VARCHAR(3),
	@CreditCardExpiry	DATE,
	@CreditCardHolder	VARCHAR(30),
	
	@IsActive			BIT
AS
BEGIN
	UPDATE tblCustomers 
	SET [Password] = @Password,
		UserLevel = @UserLevel,
		RegisteredOn = @RegisteredOn,
		
		FirstName = @FirstName,
		LastName = @LastName,				
		Email = @Email,
		Contact = @Contact,				
		DOB = @DOB,				
	
		Country = @Country,			
		City = @City,				
		[State] = @State,				
		AddressLine1 = @AddressLine1,		
		AddressLine2 = @AddressLine2,		
		PostalCode = @PostalCode,			
	
		CreditCardNumber = @CreditCardNumber,	
		CreditCardType = @CreditCardType,		
		CreditCardCVV = @CreditCardCVV,		
		CreditCardExpiry = @CreditCardExpiry,	
		CreditCardHolder = @CreditCardHolder,	
	
		IsActive = @IsActive	
	
	WHERE CustomerID = @CustomerID
END

GO
/*****************************************************************************************/
/* Update Customer */
CREATE PROCEDURE uspDeleteCustomer
	@CustomerID INT
AS
BEGIN
	DELETE FROM tblCustomers WHERE CustomerID=@CustomerID
END

GO
/*****************************************************************************************/
/* GetCustomer */
CREATE PROCEDURE uspGetCustomer
	@CustomerID INT
AS
BEGIN
	SELECT * FROM tblCustomers WHERE CustomerID = @CustomerID
END
GO
/*****************************************************************************************/
/* GetNextCustomerID */
CREATE PROCEDURE uspGetNextCustomerID
AS
BEGIN
	SELECT IDENT_CURRENT('tblCustomers') + IDENT_INCR('tblCustomers')
END
/*****************************************************************************************/
